/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package oracle_jdbc;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.Locale;
import org.omg.CORBA.DATA_CONVERSION;

/**
 *
 * @author netcracker
 */
public class Oracle_JDBC {

    public static void main(String args[]) throws IOException, SQLException {
        Locale locale = Locale.getDefault();
        System.out.println("Default Locale=" + locale);
        Locale.setDefault(Locale.ENGLISH);
        String url, user, password, resultString, reqString;
        try {

            ArrayList<Long> unpreparedTime = new ArrayList<Long>();
            ArrayList<Long> preparedTime = new ArrayList<Long>();

            //TASK 1
            Class.forName("oracle.jdbc.driver.OracleDriver");
            url = "jdbc:oracle:thin:@localhost:1521:XE";
            user = "samir";
            password = "12345";
            Connection connection = DriverManager.getConnection(url, user, password);
            Statement statement = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
            PreparedStatement stmt;
            unpreparedTime.add(System.nanoTime());
            ResultSet rs = statement.executeQuery("SELECT * FROM employees");



            while (rs.next()) {
                resultString = String.valueOf(rs.getInt(1) + " "
                        + rs.getString(2) + " " + rs.getString(2) + " "
                        + rs.getString(3) + " " + rs.getString(4) + " "
                        + rs.getDate("hire_date")) + " " + rs.getString("job_id") + " "
                        + rs.getInt("salary") + " " + rs.getDouble("COMMISSION_PCT") + " "
                        + rs.getInt("manager_id") + " " + rs.getInt("department_id");

                System.out.println(resultString);
            }
            unpreparedTime.add(System.nanoTime());

            //TASK2
            statement = connection.createStatement();

            unpreparedTime.add(System.nanoTime());
            for (int i = 300; i < 1300; i++) {
                reqString = "insert into employees"
                        + "(employee_id,first_name,last_name,email,hire_date,job_id)"
                        + "values"
                        + "(" + i + "," + "'Samir" + i + "','Ahmad" + i + "', 'ASAMIR" + i + "', '21-OCT-2013', 'IT_PROG'" + ")";
                statement.executeUpdate(reqString);
            }
            unpreparedTime.add(System.nanoTime());

            connection.commit();


            statement = connection.createStatement();
            //TASK 3
            unpreparedTime.add(System.nanoTime());

            for (int i = 300; i < 1300; i++) {
                reqString = "update employees set"
                        + " salary=123456"
                        + " where employee_id>299";
                statement.executeUpdate(reqString);
            }
            unpreparedTime.add(System.nanoTime());
            connection.commit();
            statement.close();
            //TASK 4
            unpreparedTime.add(System.nanoTime());
            for (int i = 300; i < 1300; i++) {
                reqString = "delete from employees"
                        + " where employee_id=" + i;
                statement.executeUpdate(reqString);
            }
            unpreparedTime.add(System.nanoTime());
            connection.commit();
            statement.close();

            //TASK 5
            reqString = "create table photos (employee_id number, photo blob)";
            statement.executeUpdate(reqString);
            //TASK 6    
            reqString = "insert into photos(photo) values (?)";
            stmt = connection.prepareStatement(reqString);
            File file = new File("C:\\Users\\netcracker\\Documents\\101.png");
            File file_blob = new File(file.getAbsoluteFile().toString());
            FileInputStream fis = new FileInputStream(file_blob);
            stmt.setBinaryStream(1, fis, (int) file_blob.length());
            stmt.executeQuery();

            //TASK 7

            statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            preparedTime.add(System.nanoTime());
            rs = statement.executeQuery("SELECT * FROM employees");
            while (rs.next()) {
                resultString = String.valueOf(rs.getInt(1) + " "
                        + rs.getString(2) + " " + rs.getString(2) + " "
                        + rs.getString(3) + " " + rs.getString(4) + " "
                        + rs.getDate("hire_date")) + " " + rs.getString("job_id") + " "
                        + rs.getInt("salary") + " " + rs.getDouble("COMMISSION_PCT") + " "
                        + rs.getInt("manager_id") + " " + rs.getInt("department_id");

                System.out.println(resultString);
            }
            preparedTime.add(System.nanoTime());

            //TASK 8 
            stmt = connection.prepareStatement("insert into employees"
                    + "(employee_id,first_name,last_name,email,hire_date,job_id)"
                    + "values"
                    + "(?,?,?,?,?,?)");
            preparedTime.add(System.nanoTime());
            for (int i = 1000; i < 2000; i++) {
                stmt.setInt(1, i);
                stmt.setString(2, "name" + i);
                stmt.setString(3, "last" + i);
                stmt.setString(4, "mail" + i);
                stmt.setString(5, "21-OCT-2013");
                stmt.setString(6, "IT_PROG");
                stmt.executeQuery();
            }
            preparedTime.add(System.nanoTime());



            //TASK 9

            preparedTime.add(System.nanoTime());

            stmt = connection.prepareStatement("update employees set salary=? where employee_id>299");
            for (int i = 300; i < 1300; i++) {
                stmt.setInt(1, i);
                stmt.executeUpdate();
            }
            preparedTime.add(System.nanoTime());
            connection.commit();


            //TASK 10


            preparedTime.add(System.nanoTime());
            stmt = connection.prepareStatement("delete from employees"
                    + " where employee_id=?");
            for (int i = 300; i < 1300; i++) {
                stmt.setString(1, "" + i);
                stmt.executeUpdate();
            }
            preparedTime.add(System.nanoTime());
            connection.commit();
            statement.close();

            //TASK 11
            connection.setAutoCommit(true);
            statement = connection.createStatement();

            preparedTime.add(System.nanoTime());
            for (int i = 300; i < 1300; i++) {
                reqString = "insert into employees"
                        + "(employee_id,first_name,last_name,email,hire_date,job_id)"
                        + "values"
                        + "(" + i + "," + "'Samir" + i + "','Ahmad" + i + "', 'ASAMIR" + i + "', '21-OCT-2013', 'IT_PROG'" + ")";
                statement.executeUpdate(reqString);
            }
            preparedTime.add(System.nanoTime());

            connection.commit();

            System.out.println("Prepared:");
            for (int i = 0; i < preparedTime.size() / 2; i++) {
                System.out.println("Task" + i + ":" + (preparedTime.get(i) - preparedTime.get(i + 1)));
            }

            System.out.println("unPrepared:");
            for (int i = 0; i < unpreparedTime.size() / 2; i++) {
                System.out.println("Task" + i + ":" + (unpreparedTime.get(i) - unpreparedTime.get(i + 1)));
            }

        } catch (java.lang.ClassNotFoundException er) {
            System.out.print("JDBC-Driver is wrong!\n");
            System.out.println(er.getMessage());
            System.exit(0);
        } catch (SQLException er) {
            System.out.println("Connection is wrong!");
            System.out.println(er.getMessage());
        }
    }
}
